package server;

import java.io.IOException;
import java.net.Socket;
import java.sql.*;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;


import bulkyshare.project.de.bulkyshare.activities.DbService;
import bulkyshare.project.de.bulkyshare.container.Bulk;
import bulkyshare.project.de.bulkyshare.container.Contact;
import bulkyshare.project.de.bulkyshare.container.Profil;
import bulkyshare.project.de.bulkyshare.container.Termin;

import lipermi.exception.LipeRMIException;
import lipermi.handler.CallHandler;
import lipermi.handler.CallLookup;
import lipermi.net.Server;

public class DbHandler implements DbService {
	
	private Connection conn;	
	private CallHandler callHandler;
	
	/*
	 * Statements and variables for the DB
	 */
	// Table names
    private static final String TABLE_PROFIL = "profile";
    private static final String TABLE_TERMIN = "termine";
    private static final String TABLE_PROFIL_TERMIN = "profile_termine";
    private static final String TABLE_BULK = "bulk";
    private static final String TABLE_CONTACTS = "contacts";

    // Column names for table PROFIL
    private static final String PROFIL_ID = "id";
    private static final String PROFIL_MAIL = "mail";
    private static final String PROFIL_PASS = "password";

    // Column names for table TERMIN
    private static final String TERMIN_ID = "id";
    private static final String TERMIN_DATE = "date";
    private static final String TERMIN_STREET = "street";
    private static final String TERMIN_PLZ = "plz";
    private static final String TERMIN_LONGITUDE = "longitude";
    private static final String TERMIN_LATITUDE = "latitude";
    private static final String TERMIN_COST_TYPE = "cost_type";
    private static final String TERMIN_PRICE = "price";
    private static final String TERMIN_EXTRA_INFO = "extra_info";

    // Column names for table PROFIL_TERMIN
    private static final String PROFIL_TERMIN_ID = "id";
    private static final String PROFIL_TERMIN_PID = "profil_id";
    private static final String PROFIL_TERMIN_TID = "termin_id";

    // Column names for table BULK
    private static final String BULK_ID = "id";
    private static final String BULK_TID = "termin_id";
    private static final String BULK_NAME = "name";
    private static final String BULK_DESCR = "description";
    private static final String BULK_COND = "condition";

    // Column names for table CONTACTS
    private static final String CONTACTS_ID = "id";
    private static final String CONTACTS_TID = "termin_id";
    private static final String CONTACTS_CON = "contact";
    private static final String CONTACTS_TYPE = "type";

    // Table Create statements
    private static final String CREATE_TABLE_PROFIL = "CREATE TABLE "
            + TABLE_PROFIL + "(" + PROFIL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + PROFIL_MAIL + " TEXT," + PROFIL_PASS + " TEXT" + ")";

    private static final String CREATE_TABLE_TERMIN = "CREATE TABLE "
            + TABLE_TERMIN + "(" + TERMIN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + TERMIN_DATE + " DATETIME," + TERMIN_STREET + " TEXT,"
            + TERMIN_PLZ + " INTEGER," + TERMIN_COST_TYPE + " TEXT,"
            + TERMIN_PRICE + " INTEGER," + TERMIN_EXTRA_INFO + " TEXT,"
            + TERMIN_LONGITUDE + " REAL," + TERMIN_LATITUDE + " REAL" + ")";

    private static final String CREATE_TABLE_PROFIL_TERMIN = "CREATE TABLE "
            + TABLE_PROFIL_TERMIN + "(" + PROFIL_TERMIN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + PROFIL_TERMIN_PID + " INTEGER," + PROFIL_TERMIN_TID + " INTEGER" + ")";

    private static final String CREATE_TABLE_BULK = "CREATE TABLE "
            + TABLE_BULK + "(" + BULK_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + BULK_TID + " INTEGER," + BULK_NAME + " TEXT,"
            + BULK_DESCR + " TEXT," + BULK_COND + " TEXT" + ")";

    private static final String CREATE_TABLE_CONTACTS = "CREATE TABLE "
            + TABLE_CONTACTS + "(" + CONTACTS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + CONTACTS_TID + " INTEGER," + CONTACTS_CON + " TEXT,"
            + CONTACTS_TYPE + " TEXT" + ")";
    /*
     * End of statements
     */
    
    /*
     * Constructor which establishes the Server functionality for the
     * Database access through RMI.
     */
    public DbHandler() {
    	callHandler = new CallHandler();
    	Server server = new Server();
    	
    	int port = 12345; // Server port
        
        try {
            callHandler.registerGlobal(DbService.class, this);
            server.bind(port, callHandler);
            System.out.println("Running. . .");
        } catch (LipeRMIException e) {
        	System.err.println(e.getClass().getName() + ": " + e.getMessage());
        } catch (IOException e) {
        	System.err.println(e.getClass().getName() + ": " + e.getMessage());
        }
    }
    
    public static void main(String[] args) {
        new DbHandler();
    }
    
//    public static void main(String[] args) {
//    	DbHandler db = new DbHandler();
//    	db.openDB();
//    	
//    	Profil p1 = new Profil();
//    	p1.setMail("xx@web.de");
//    	p1.setPassword("abc123");
//    	
//    	db.createProfil(p1);
//    	
//    	db.getProfil("xx@web.de");
//
//    	db.closeDB();
//    }
    
    // Method to convert a date into a certain format.
    private String formatDate(String date) {
        try {
            Date format_date = new SimpleDateFormat("yyyy-MM-dd", Locale.GERMANY).parse(date);
            DateFormat df = DateFormat.getDateInstance(DateFormat.MEDIUM, Locale.GERMANY);
            return df.format(format_date);
        } catch (ParseException e) {
        	System.err.println(e.getClass().getName() + ": " + e.getMessage());
        }
        return "";
    }
    
    // Method to open the Database connection. Handles also the sqlite driver.
    public boolean openDB() {
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:bulky.db");
			Socket clientSocket = CallLookup.getCurrentSocket();
			System.out.println("Client " + clientSocket.getRemoteSocketAddress() + " is accessing the Database.");
			return true;
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			return false;
		}
    }
    
    // Method to close the Database connection.
    public void closeDB() {
    	try {
    		Socket clientSocket = CallLookup.getCurrentSocket();
    		conn.close();
			System.out.println("Client " + clientSocket.getRemoteSocketAddress() + " closed the DB connection.");		
    	} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
    }
    
    /*
     *  Method to create the necessary tables for the DB.
     *  Can only be called ONCE if the tables don't exist in the DB yet.
     */
    public void createTables() {	
		try {
			Statement stmt = null;
	    	stmt = conn.createStatement();
			stmt.executeUpdate(CREATE_TABLE_PROFIL);
			stmt.executeUpdate(CREATE_TABLE_TERMIN);
			stmt.executeUpdate(CREATE_TABLE_PROFIL_TERMIN);
			stmt.executeUpdate(CREATE_TABLE_BULK);
			stmt.executeUpdate(CREATE_TABLE_CONTACTS);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
    /*
     * This method is used to return the auto-increment primary keys
     * for new entries in the DB. Should there be an error with the
     * SQL statement, which means that a primary key couldn't get
     * created, it will return -1 as an error value.
     */
    private int getPrimaryKey() {
    	int id = -1;
    	try {
    		// A prepared Statement is for this procedure not necessary.
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()");
			while (rs.next()) {
				id = rs.getInt(1);	
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return id;
    }
    
    /**********************************************************************************************
     *           ALL METHODS FOR THE TABLE 'PROFIL'
     **********************************************************************************************/
    // Method to fetch a profile entry by its ID from the DB.
    public Profil getProfil(int profil_id) {
    	System.out.println("GET PROFIL BY ID called.");
    	Profil p = new Profil();
    	try {
    		String sql = "SELECT * FROM " + TABLE_PROFIL + " WHERE " + PROFIL_ID + "=?";
    		System.out.println(sql);
    		PreparedStatement ps = conn.prepareStatement(sql);
    		ps.setInt(1, profil_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(PROFIL_ID);
				String username = rs.getString(PROFIL_MAIL);
				String pass = rs.getString(PROFIL_PASS);
				
				p.setId(id);
				p.setMail(username);
				p.setPassword(pass);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return p;
    }
    
    // Method to fetch a profile entry by its name (mail address) from the DB.
    public Profil getProfil(String mail) {
    	System.out.println("GET PROFIL BY MAIL called.");
    	Profil p = new Profil();
    	p.setId(-1);
    	p.setMail("");
    	p.setPassword("");
    	try {
    		String sql = "SELECT * FROM " + TABLE_PROFIL + " WHERE " + PROFIL_MAIL + "=?";
    		System.out.println(sql);
    		PreparedStatement ps = conn.prepareStatement(sql);
    		ps.setString(1, mail);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(PROFIL_ID);
				String username = rs.getString(PROFIL_MAIL);
				String pass = rs.getString(PROFIL_PASS);
				
				System.out.println("id: " + id);
				System.out.println("username: " + username);
				System.out.println("password: " + pass);
				
				p.setId(id);
				p.setMail(username);
				p.setPassword(pass);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return p;
    }
	
    // Method to create a profile in the DB.
	@Override
	public int createProfil(Profil p) {
		System.out.println("CREATE PROFIL called.");
		int p_id = 0;
		try {
			String sql = "INSERT INTO " + TABLE_PROFIL + " (" + PROFIL_MAIL + ", " + PROFIL_PASS + ") " +
					 "VALUES (?, ?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, p.getMail());
			ps.setString(2, p.getPassword());
			System.out.println(sql);
			ps.executeUpdate();
			p_id = getPrimaryKey();
			System.out.println("New user registered with ID " + p_id);
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return p_id;
	}
	
	// Method to update a profile in the DB.
	@Override
	public int updateProfil(Profil p) {
		System.out.println("UPDATE PROFIL called.");
		int success = 0;
		try {
			String sql = "UPDATE " + TABLE_PROFIL + 
					 " set " + PROFIL_MAIL + "=? , " + PROFIL_PASS + " =?" +
					 " WHERE " + PROFIL_ID + "=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, p.getMail());
			ps.setString(2, p.getPassword());
			ps.setInt(3, p.getId());
			System.out.println(sql);
			success = ps.executeUpdate();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return success;
	}
	
	// Method to delete a profile (all 'Termine' (appointments) will be deleted as well).
	@Override
	public void deleteProfil(int profil_id) {
		System.out.println("DELETE PROFIL called.");
		// First we have to remove all the Termine/appointments connected to the profile.
        // For that purpose we use the getAllTermineByProfile Method.
		List<Termin> termine = new ArrayList<Termin>();
		termine = getAllTermineByProfilePrv(profil_id);
		int termineCount = termine.size();
		
		PreparedStatement ps;
		
		try {
			for (int i=0; i<termineCount; i++) {
				String sql;
				// Before we can delete the appointment we need to remove the bulk items and the contacts.
				sql = "DELETE from " + TABLE_CONTACTS + " where " + CONTACTS_TID + "=?";
				System.out.println(sql);
				ps = conn.prepareStatement(sql);
				ps.setInt(1, termine.get(i).getId());
				ps.executeUpdate();
				
				sql = "DELETE from " + TABLE_BULK + " where " + BULK_TID + "=?";
				System.out.println(sql);
				ps = conn.prepareStatement(sql);
				ps.setInt(1, termine.get(i).getId());
				ps.executeUpdate();
				
				// The entry in the PROFIL_TERMIN table gets removed.
				sql = "DELETE from " + TABLE_PROFIL_TERMIN + " where " + PROFIL_TERMIN_TID + "=?";
				System.out.println(sql);
				ps = conn.prepareStatement(sql);
				ps.setInt(1, termine.get(i).getId());
				ps.executeUpdate();
				
				// ..then the actual entry in the TERMIN table.
				sql = "DELETE from " + TABLE_TERMIN + " where " + TERMIN_ID + "=?";
				System.out.println(sql);
				ps = conn.prepareStatement(sql);
				ps.setInt(1, termine.get(i).getId());
				ps.executeUpdate();
			}
			
			// Now the profile is free of any appointment relations and can be removed.
			String sql = "DELETE from " + TABLE_PROFIL + " where " + PROFIL_ID + "=?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			ps.setInt(1, profil_id);
			ps.executeUpdate();

		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
	}
	
	/**********************************************************************************************
     *           ALL METHODS FOR THE TABLE 'TERMIN'
     **********************************************************************************************/
	
	// Method to create a 'Termin' (appointment) in the DB.
	@Override
	public int createTermin(List<String> terminList) {
		System.out.println("CREATE TERMIN called");
		int t_id = 0;
		try {
			String sql = "INSERT INTO " + TABLE_TERMIN + " (" + TERMIN_DATE + ", " + TERMIN_STREET + ", " + TERMIN_PLZ + ", " + TERMIN_COST_TYPE + ", " + TERMIN_PRICE + ", " + TERMIN_EXTRA_INFO + ", " + TERMIN_LONGITUDE + ", " + TERMIN_LATITUDE + ") " +
						 "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
			System.out.println(sql);
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, terminList.get(0));
			ps.setString(2, terminList.get(1));
			ps.setString(3, terminList.get(2));
			ps.setString(4, terminList.get(3));
			ps.setInt(5, Integer.parseInt(terminList.get(4)));
			ps.setString(6, terminList.get(5));
			ps.setDouble(7, Double.parseDouble(terminList.get(6)));
			ps.setDouble(8, Double.parseDouble(terminList.get(7)));
			ps.executeUpdate();
			t_id = getPrimaryKey();
			System.out.println("New termin saved with ID " + t_id);
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return t_id;
	}
	
	// Method to fetch a 'Termin' (appointment) by its ID from the DB.
	@Override
	public List<String> getTermin(int termin_id) {
		System.out.println("SELECT TERMIN called.");
		Termin t = new Termin();
    	try {
    		String sql = "SELECT * FROM " + TABLE_TERMIN + " WHERE " + TERMIN_ID + " =?";
    		PreparedStatement ps = conn.prepareStatement(sql);
    		System.out.println(sql);
    		ps.setInt(1, termin_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(TERMIN_ID);
				String date = rs.getString(TERMIN_DATE);
				String street = rs.getString(TERMIN_STREET);
				String plz = rs.getString(TERMIN_PLZ);
				String ct = rs.getString(TERMIN_COST_TYPE);
				int price = rs.getInt(TERMIN_PRICE);
				String extrainfo = rs.getString(TERMIN_EXTRA_INFO);
				double longitude = rs.getDouble(TERMIN_LONGITUDE);
				double latitude = rs.getDouble(TERMIN_LATITUDE);
				
				t.setId(id);
				t.setDate(formatDate(date));
				t.setStreet(street);
				t.setPlz(plz);
				t.setCost_type(ct);
				t.setPrice(price);
				t.setExtra_info(extrainfo);
				t.setLongitude(longitude);
				t.setLatitude(latitude);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
    	List<String> terminList = new ArrayList<String>();
    	
    	terminList.add(String.valueOf(t.getId()));
    	terminList.add(String.valueOf(t.getDate()));
    	terminList.add(String.valueOf(t.getStreet()));
    	terminList.add(String.valueOf(t.getPlz()));
    	terminList.add(String.valueOf(t.getCost_type()));
    	terminList.add(String.valueOf(t.getPrice()));
    	terminList.add(String.valueOf(t.getExtra_info()));
    	terminList.add(String.valueOf(t.getLongitude()));
    	terminList.add(String.valueOf(t.getLatitude()));
		return terminList;
	}
	
	// Method to fetch all 'Termine' (appointments) from the DB.
	@Override
	public List<String> getAllTermine(int profil_id) {
		System.out.println("SELECT TERMINE called.");
		List<Termin> terminList = new ArrayList<Termin>();
		List<String> terminListStr = new ArrayList<String>();
    	try {
    		String sql = "SELECT * FROM " + TABLE_TERMIN + " ORDER BY " + TERMIN_DATE;
    		PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(TERMIN_ID);
				String date = rs.getString(TERMIN_DATE);
				String street = rs.getString(TERMIN_STREET);
				String plz = rs.getString(TERMIN_PLZ);
				String ct = rs.getString(TERMIN_COST_TYPE);
				int price = rs.getInt(TERMIN_PRICE);
				String extrainfo = rs.getString(TERMIN_EXTRA_INFO);
				double longitude = rs.getDouble(TERMIN_LONGITUDE);
				double latitude = rs.getDouble(TERMIN_LATITUDE);
			
				Termin t_tmp = new Termin();
				t_tmp.setId(id);
				t_tmp.setDate(formatDate(date));
				t_tmp.setStreet(street);
				t_tmp.setPlz(plz);
				t_tmp.setCost_type(ct);
				t_tmp.setPrice(price);
				t_tmp.setExtra_info(extrainfo);
				t_tmp.setLongitude(longitude);
				t_tmp.setLatitude(latitude);
				terminList.add(t_tmp);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
    	
    	// terminListByProfile containts all the Termine which belong to the user.
    	// These ones will be removed in terminList by the method removeAll.
    	List<Termin> terminListByProfile = new ArrayList<Termin>();
    	terminListByProfile = getAllTermineByProfilePrv(profil_id);
    	
    	for (int k=0; k<terminList.size(); k++) {
    		for (int l=0; l<terminListByProfile.size(); l++) {
    			if (terminList.get(k).getId() == terminListByProfile.get(l).getId()) {
    				terminList.remove(k);
    			}
    		}
    	}
    	
    	//terminList.removeAll(terminListByProfile);
    	
    	terminListStr.add(String.valueOf(terminList.size()));
    	
    	for (int i=0; i<terminList.size(); i++) {
    		terminListStr.add(String.valueOf(terminList.get(i).getId()));
        	terminListStr.add(String.valueOf(terminList.get(i).getDate()));
        	terminListStr.add(String.valueOf(terminList.get(i).getStreet()));
        	terminListStr.add(String.valueOf(terminList.get(i).getPlz()));
        	terminListStr.add(String.valueOf(terminList.get(i).getCost_type()));
        	terminListStr.add(String.valueOf(terminList.get(i).getPrice()));
        	terminListStr.add(String.valueOf(terminList.get(i).getExtra_info()));
        	terminListStr.add(String.valueOf(terminList.get(i).getLongitude()));
        	terminListStr.add(String.valueOf(terminList.get(i).getLatitude()));
        	terminListStr.add("END");
    	}	
    	terminListStr.add("ENDOFLIST");
		return terminListStr;
	}
	
	// This method fetches all 'Termine' (appointments) by a specific user from the DB.
	@Override
	public List<String> getAllTermineByProfile(int profil_id) {
		System.out.println("SELECT ALL TERMINE BY PROFILE called.");
		List<Termin> terminList = new ArrayList<Termin>();
		List<String> terminListStr = new ArrayList<String>();
    	try {
    		String sql = "SELECT * FROM " + TABLE_TERMIN
	                + " WHERE " + TERMIN_ID + " IN (SELECT " + PROFIL_TERMIN_TID
	                + " FROM " + TABLE_PROFIL_TERMIN
	                + " WHERE " + PROFIL_TERMIN_PID + " IN (SELECT " + PROFIL_ID
	                + " FROM " + TABLE_PROFIL + " WHERE " + PROFIL_ID + "=?" + ")) ORDER BY " + TERMIN_DATE;
    		PreparedStatement ps = conn.prepareStatement(sql);
    		ps.setInt(1, profil_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(TERMIN_ID);
				String date = rs.getString(TERMIN_DATE);
				String street = rs.getString(TERMIN_STREET);
				String plz = rs.getString(TERMIN_PLZ);
				String ct = rs.getString(TERMIN_COST_TYPE);
				int price = rs.getInt(TERMIN_PRICE);
				String extrainfo = rs.getString(TERMIN_EXTRA_INFO);
				double longitude = rs.getDouble(TERMIN_LONGITUDE);
				double latitude = rs.getDouble(TERMIN_LATITUDE);
			
				Termin t_tmp = new Termin();
				t_tmp.setId(id);
				t_tmp.setDate(formatDate(date));
				t_tmp.setStreet(street);
				t_tmp.setPlz(plz);
				t_tmp.setCost_type(ct);
				t_tmp.setPrice(price);
				t_tmp.setExtra_info(extrainfo);
				t_tmp.setLongitude(longitude);
				t_tmp.setLatitude(latitude);
				terminList.add(t_tmp);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
    	
    	terminListStr.add(String.valueOf(terminList.size()));
    	
    	for (int i=0; i<terminList.size(); i++) {
    		terminListStr.add(String.valueOf(terminList.get(i).getId()));
        	terminListStr.add(String.valueOf(terminList.get(i).getDate()));
        	terminListStr.add(String.valueOf(terminList.get(i).getStreet()));
        	terminListStr.add(String.valueOf(terminList.get(i).getPlz()));
        	terminListStr.add(String.valueOf(terminList.get(i).getCost_type()));
        	terminListStr.add(String.valueOf(terminList.get(i).getPrice()));
        	terminListStr.add(String.valueOf(terminList.get(i).getExtra_info()));
        	terminListStr.add(String.valueOf(terminList.get(i).getLongitude()));
        	terminListStr.add(String.valueOf(terminList.get(i).getLatitude()));
        	terminListStr.add("END");
    	}
    	terminListStr.add("ENDOFLIST");
		return terminListStr;
	}
	
	// This method fetches all 'Termine' (appointments) by a specific user from the DB.
	private List<Termin> getAllTermineByProfilePrv(int profil_id) {
		System.out.println("SELECT ALL TERMINE BY PROFILE called.");
		List<Termin> terminList = new ArrayList<Termin>();
    	try {
    		String sql = "SELECT * FROM " + TABLE_TERMIN
	                + " WHERE " + TERMIN_ID + " IN (SELECT " + PROFIL_TERMIN_TID
	                + " FROM " + TABLE_PROFIL_TERMIN
	                + " WHERE " + PROFIL_TERMIN_PID + " IN (SELECT " + PROFIL_ID
	                + " FROM " + TABLE_PROFIL + " WHERE " + PROFIL_ID + "=?" + ")) ORDER BY " + TERMIN_DATE;
    		PreparedStatement ps = conn.prepareStatement(sql);
    		ps.setInt(1, profil_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(TERMIN_ID);
				String date = rs.getString(TERMIN_DATE);
				String street = rs.getString(TERMIN_STREET);
				String plz = rs.getString(TERMIN_PLZ);
				String ct = rs.getString(TERMIN_COST_TYPE);
				int price = rs.getInt(TERMIN_PRICE);
				String extrainfo = rs.getString(TERMIN_EXTRA_INFO);
				double longitude = rs.getDouble(TERMIN_LONGITUDE);
				double latitude = rs.getDouble(TERMIN_LATITUDE);
			
				Termin t_tmp = new Termin();
				t_tmp.setId(id);
				t_tmp.setDate(formatDate(date));
				t_tmp.setStreet(street);
				t_tmp.setPlz(plz);
				t_tmp.setCost_type(ct);
				t_tmp.setPrice(price);
				t_tmp.setExtra_info(extrainfo);
				t_tmp.setLongitude(longitude);
				t_tmp.setLatitude(latitude);
				terminList.add(t_tmp);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}   
		return terminList;
	}
	
	// Method to delete a Termin.
	@Override
	public void deleteTermin(int termin_id) {
		System.out.println("DELETE TERMIN called.");
		PreparedStatement ps;
		try {
			String sql;
			sql = "DELETE from " + TABLE_CONTACTS + " where " + CONTACTS_TID + "=?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			ps.setInt(1, termin_id);
			ps.executeUpdate();
			
			sql = "DELETE from " + TABLE_BULK + " where " + BULK_TID + "=?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			ps.setInt(1, termin_id);
			ps.executeUpdate();
			
			sql = "DELETE from " + TABLE_PROFIL_TERMIN + " where " + PROFIL_TERMIN_TID + "=?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			ps.setInt(1, termin_id);
			ps.executeUpdate();
			
			sql = "DELETE from " + TABLE_TERMIN + " where " + TERMIN_ID + "=?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			ps.setInt(1, termin_id);
			ps.executeUpdate();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}		
	}
	
	// Method to update a 'Termin' (appointment) in the DB.
	@Override
	public int updateTermin(List<String> terminList) {
		System.out.println("UPDATE TERMIN called.");
		int success = 0;
		try {
			String sql = "UPDATE " + TABLE_TERMIN + 
					 " set " + TERMIN_DATE + " =? , " + TERMIN_STREET + " =? , " + TERMIN_PLZ + " =? , " + TERMIN_COST_TYPE + " =? , " + TERMIN_PRICE + " =? , " + TERMIN_EXTRA_INFO + " =? , " + TERMIN_LONGITUDE + " =? , " + TERMIN_LATITUDE + " =?" +
					 " WHERE " + TERMIN_ID + " =?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, terminList.get(0));
			ps.setString(2, terminList.get(1));
			ps.setString(3, terminList.get(2));
			ps.setString(4, terminList.get(3));
			ps.setInt(5, Integer.parseInt(terminList.get(4)));
			ps.setString(6, terminList.get(5));
			ps.setDouble(7, Double.parseDouble(terminList.get(6)));
			ps.setDouble(8, Double.parseDouble(terminList.get(7)));
			ps.setInt(9, Integer.parseInt(terminList.get(8))); // ID des Termins
			
			System.out.println(sql);
			success = ps.executeUpdate();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return success;
	}
	
	 /**********************************************************************************************
     *           ALL METHODS FOR THE TABLE 'PROFIL_TERMIN'
     **********************************************************************************************/
	
	/*
	 * Method to assign a 'Termin' (appointment) to a profile in the DB
	 * IMPORTANT: CURRENTLY THIS METHOD HAS TO BE CALLED MANUALLY AFTER A USER
	 * CREATES A TERMIN/APPOINTMENT.
	 */
	@Override
	public int createProfilTerminEntry(int profil_id, int termin_id) {
		System.out.println("CREATE PROFIL_TERMIN called.");
		int pt_id = 0;
		try {
			String sql = "INSERT INTO " + TABLE_PROFIL_TERMIN + " (" + PROFIL_TERMIN_PID + ", " + PROFIL_TERMIN_TID + ") " +
					 "VALUES (?, ?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, profil_id);
			ps.setInt(2, termin_id);
			System.out.println(sql);
			ps.executeUpdate();
			pt_id = getPrimaryKey();
			System.out.println("New profil_termin entry saved with ID " + pt_id);
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return pt_id;
	}
	
	/**********************************************************************************************
     *           ALL METHODS FOR THE TABLE 'BULK'
     **********************************************************************************************/
	
	// Method to create a list of Bulk items in the DB.
	@Override
	public List<Integer> createBulk(List<Bulk> bulkList, int termin_id) {
		System.out.println("CREATE BULK called");
		//int b_ids[] = new int[bulkList.size()];
		List<Integer> bulkIds = new ArrayList<Integer>();
		try {
			String sql;
			for (int i=0; i<bulkList.size(); i++) {
				sql = "INSERT INTO " + TABLE_BULK + " (" + BULK_TID + ", " + BULK_NAME + ", " + BULK_DESCR + ", " + BULK_COND + ") " +
						  "VALUES (?, ?, ?, ?)";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, termin_id);
				ps.setString(2, bulkList.get(i).getName());
				ps.setString(3, bulkList.get(i).getDescription());
				ps.setString(4, bulkList.get(i).getCondition());
				System.out.println(sql);
				ps.executeUpdate(sql);
				int b_id = getPrimaryKey();
				System.out.println("New bulk item saved with ID " + b_id);
				bulkIds.add(b_id);
			}	
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return bulkIds;
	}
	
	// Method to return a list containing all Bulk Items for a 'Termin' (appointment).
	@Override
	public List<Bulk> getBulks(int termin_id) {
		System.out.println("SELECT BULK called.");
		List<Bulk> bulkList = new ArrayList<Bulk>();
    	try {
    		String sql = "SELECT * FROM " + TABLE_BULK + " WHERE " + BULK_TID + " =?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, termin_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(BULK_ID);
				int tid = rs.getInt(BULK_TID);
				String name = rs.getString(BULK_NAME);
				String descr = rs.getString(BULK_DESCR);
				String cond = rs.getString(BULK_COND);
				
				Bulk b_tmp = new Bulk();
				b_tmp.setId(id);
				b_tmp.setTermin_id(tid);
				b_tmp.setName(name);
				b_tmp.setDescription(descr);
				b_tmp.setCondition(cond);
				bulkList.add(b_tmp);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return bulkList;
	}
	
	// Method to update a 'Contact' in the DB.
	@Override
	public int updateBulk(Bulk b, int termin_id) {
		System.out.println("UPDATE BULK called.");
		int success = 0;
		try {
			String sql = "UPDATE " + TABLE_BULK + 
					 " set " + BULK_TID + " =? , " + BULK_NAME + " =? , " + BULK_DESCR + " =? , " + BULK_COND + " =?" +
					 " WHERE " + BULK_ID + " =?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, termin_id);
			ps.setString(2, b.getName());
			ps.setString(3, b.getDescription());
			ps.setString(4, b.getCondition());
			ps.setInt(5, b.getId());
			System.out.println(sql);
			success = ps.executeUpdate();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return success;
	}
	
	// Method to delete a Bulk item.
	@Override
	public void deleteBulk(int bulk_id) {
		System.out.println("DELETE BULK called.");
		try {
			String sql = "DELETE from " + TABLE_BULK + " where " + BULK_ID + "=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, bulk_id);
			System.out.println(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
	}
	
	/**********************************************************************************************
     *           ALL METHODS FOR THE TABLE 'CONTACTS'
     **********************************************************************************************/
	
	// Method to create a contact entry for a 'Termin' (appointment).
	@Override
	public int createContact(Contact contact, int termin_id) {
		System.out.println("CREATE CONTACT called.");
		int c_id = 0;
		try {
			String sql = "INSERT INTO " + TABLE_CONTACTS + " (" + CONTACTS_TID + ", " + CONTACTS_CON + ", " + CONTACTS_TYPE + ") " +
					 "VALUES (?, ?, ?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, termin_id);
			ps.setString(2, contact.getContact());
			ps.setString(3, contact.getType());
			System.out.println(sql);
			ps.executeUpdate();
			c_id = getPrimaryKey();
			System.out.println("New contact entry saved with ID " + c_id);
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return c_id;
	}
	
	// Method to return a list containing all contact entries for a 'Termin' (appointment).
	@Override
	public List<Contact> getContacts(int termin_id) {
		System.out.println("SELECT CONTACT called.");
		List<Contact> contactList = new ArrayList<Contact>();
    	try {
    		String sql = "SELECT * FROM " + TABLE_CONTACTS + " WHERE " + CONTACTS_TID + " =?";
    		System.out.println(sql);
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, termin_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(CONTACTS_ID);
				int tid = rs.getInt(CONTACTS_TID);
				String con = rs.getString(CONTACTS_CON);
				String type = rs.getString(CONTACTS_TYPE);
				
				Contact c_tmp = new Contact();
				c_tmp.setId(id);
				c_tmp.setTermin_id(tid);
				c_tmp.setContact(con);
				c_tmp.setType(type);
				contactList.add(c_tmp);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return contactList;
	}
	
	// Method to update a 'Contact' in the DB.
	@Override
	public int updateContact(Contact contact, int termin_id) {
		System.out.println("UPDATE CONTACT called.");
		int success = 0;
		try {
			String sql = "UPDATE " + TABLE_CONTACTS + 
					 " set " + CONTACTS_TID + " =? , " + CONTACTS_CON + " =? , " + CONTACTS_TYPE + " =?" +
					 " WHERE " + CONTACTS_ID + " =?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, termin_id);
			ps.setString(2, contact.getContact());
			ps.setString(3, contact.getType());
			ps.setInt(4, contact.getId());
			System.out.println(sql);
			success = ps.executeUpdate();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
		return success;
	}
	
	// Method to delete a Contact.
	@Override
	public void deleteContact(int contact_id) {
		System.out.println("DELETE CONTACT called.");
		try {
			String sql = "DELETE from " + TABLE_CONTACTS + " where " + CONTACTS_ID + "=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, contact_id);
			System.out.println(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
		}
	}
	
	// Test Method for RMI functionality
	@Override
	public String halloWelt() {
		System.out.println("halloWelt wurde von einem Client aufgerufen!");
		Socket clientSocket = CallLookup.getCurrentSocket();
		System.out.println("Client: " + clientSocket.getRemoteSocketAddress());
		return "Hallo vom RMI Server!!!";
	}
}
